package cn.edu.sdjzu.xg.eagle.dao;

import cn.edu.sdjzu.xg.eagle.entity.School;
import cn.edu.sdjzu.xg.eagle.util.JdbcPoolUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.TreeSet;

/**
 * @author tg.si@188.com
 * @project xg22
 * @date 2025/3/15
 */
public class SchoolDaoImpl implements SchoolDaoInterface {

    @Override
    public School findById(Long id, Connection connection) throws SQLException {
        School desiredSchool = null;
        String sql = "SELECT * FROM bysj.school WHERE id = ?";
        try (PreparedStatement pstmt_select_id = connection.prepareStatement(sql)) {
            pstmt_select_id.setLong(1, id);
            try (ResultSet resultSet_select_id = pstmt_select_id.executeQuery()) {
                if (resultSet_select_id.next()) {
                    String description = resultSet_select_id.getString("description");
                    String no = resultSet_select_id.getString("no");
                    String remarks = resultSet_select_id.getString("remarks");
                    desiredSchool = new School(id, description, no, remarks);
                }
            }
        }
        return desiredSchool;
    }

    @Override
    public Collection<School> findAll(Connection connection) throws SQLException {
        Collection<School> desiredSchools = new TreeSet<>();
        try (PreparedStatement pstmt_select_all = connection.prepareStatement("SELECT * FROM School ")) {
            try (ResultSet resultSet = pstmt_select_all.executeQuery()) {
                while (resultSet.next()) {
                    long id = resultSet.getLong("id");
                    String description = resultSet.getString("description");
                    String no = resultSet.getString("no");
                    String remarks = resultSet.getString("remarks");
                    School desiredSchool = new School(id, description, no, remarks);
                    desiredSchools.add(desiredSchool);
                }
            }
        }
        return desiredSchools;
    }

    @Override
    public boolean update(School school, Connection connection) throws SQLException {
        try (PreparedStatement pstmt_update = connection.prepareStatement("UPDATE school SET description = ?, no = ?, remarks = ? WHERE id = ?")) {
            pstmt_update.setString(1, school.getDescription());
            pstmt_update.setString(2, school.getNo());
            pstmt_update.setString(3, school.getRemarks());
            pstmt_update.setLong(4, school.getId());
            int rowAffected = pstmt_update.executeUpdate();
            return rowAffected > 0;
        }
    }

    @Override
    public boolean save(School school, Connection connection) throws SQLException {
        try (PreparedStatement pstmt_update = connection.prepareStatement("INSERT INTO school (description, no, remarks) VALUES (?, ?, ?)")) {
            pstmt_update.setString(1, school.getDescription());
            pstmt_update.setString(2, school.getNo());
            pstmt_update.setString(3, school.getRemarks());
            int rowAffected = pstmt_update.executeUpdate();
            return rowAffected > 0;
        }
    }

    @Override
    public boolean delete(Long id, Connection connection) throws SQLException {
        try (PreparedStatement pstmt_delete = connection.prepareStatement("DELETE FROM school WHERE id = ?")) {
            pstmt_delete.setLong(1, id);
            int rowAffected = pstmt_delete.executeUpdate();
            return rowAffected > 0;
        }
    }

    @Override
    public boolean delete(School school, Connection connection) throws SQLException {
        return this.delete(school.getId(), connection);
    }

    public static void main(String[] args) throws SQLException {
        SchoolDaoInterface schoolDao = new SchoolDaoImpl();
        try (Connection connection = JdbcPoolUtil.getConn()) {
            System.out.println(schoolDao.findById(2L, connection));
        }
    }
}